# -*- coding: utf-8 -*-

#import from stox
import MySQLdb
import glob
import os


# PARAMS
mydir = "./stox/spiders/bs"
table = "balance_sheet_stox"


# connect mysql
db = MySQLdb.connect(host="localhost", port=3306, user="root",
                     passwd="123456", db="quant_data", charset='utf8')
cursor=db.cursor()

#get the map of security_id and ticker 
temp = cursor.execute("""SELECT security_id, short_name FROM `security` where `security_type`=1 """)
temp = cursor.fetchall()
ids = dict((y, x) for x, y in temp) #create the ID dict -> can call ids['ACB']


os.chdir(mydir)             #change to current dir
files = glob.glob("ABI.csv")  #find all file with csv extension in current dir
for ffile in files:
    #fname = mydir + "/" +files
    with open(ffile) as f:   # within "with" keyword, it handle the open/close file
        content = f.readlines()        
        print("Processing %s ... " %ffile),        
        numlines = len(content)
        for n in range(numlines):
            s = content[n].replace('\n','').split(",")
            #print s            
            ticker = s[0].replace('"','')
            
            #remove the % sign 
            for i in range(len(s)):
                if(s[i].find('%')!=-1):
                    s[i] = str(float(s[i].replace('%',''))/100)
            
            # replace to get sql 
            s[0] = str(ids[ticker])
            sql = "INSERT INTO `" + table +"` VALUES ('" + "','".join(s) + "')"
            if(table == 'balance_sheet_stox'):
                sql = "INSERT INTO `" + table +"` VALUES ('" + "','".join(s) + "',null)"
            sql = sql.replace("''","NULL")
            #print sql
            try:                
                temp = cursor.execute(sql)  #execute sql
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0],e.args[1])
                continue    
    
        print "Done"    
        
cursor.close()  #close the cursor
db.commit()     #commit, must have or else the databases won't change
db.close()      #close the connection
print "Done import"